SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

IF EXISTS (	SELECT *
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_NAME = 'GetSimilarProducts'
			AND ROUTINE_TYPE = 'PROCEDURE' )
BEGIN
	PRINT 'Dropping stored procedure GetSimilarProducts.';
	DROP PROCEDURE [dbo].[GetSimilarProducts];
END

GO
-------------------------------------------------------------------------------
--	METADATA AND OTHER MESSAGES ABOVE.
--	BEGIN CREATE STATEMENTS.
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[GetSimilarProducts] 
	@ProductID INT
	, @CategoryID INT
	, @RecordToReturn INT	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF @CategoryID = 0 -- Get Top Viewed Products Among All Category
	BEGIN
		SELECT
		P.ID
		, P.Code
		, P.UPC
		, P.Model
		, P.Name
		, P.Description
		, A.Manufacturer	 
		, A.Company
		, P.Price
		, P.ProductUrl
		, (SELECT TOP 1 ProductImage.Thumbnail FROM ProductImage WHERE ProductImage.ProductID = P.ID) as [ThumbImage]
		,A.[Total Viewed]
		FROM
		(
		SELECT TOP (@RecordToReturn)
		P.ID, M.Name AS [Manufacturer], C.Name AS [Company]
		,ISNULL(COUNT(P.ID),0) AS [Total Viewed]  
		FROM Product P
		INNER JOIN Manufacturer M ON M.ID = P.ManufacturerID 
		LEFT JOIN ProductVisitDetails PVD ON P.ID = PVD.ProductID
		INNER JOIN Company C ON P.CompanyID = C.ID
		WHERE --P.CategoryID = 16
		--AND P.ID NOT IN (11)
		--AND 
		P.IsActive = 1
		AND P.IsDeleted = 0
		AND C.IsActive = 1
		GROUP BY P.ID,M.Name, C.Name
		ORDER BY [Total Viewed] DESC
		) A INNER JOIN Product P ON A.ID = P.ID
	END
	ELSE	---Category Specific Search
	BEGIN
		SELECT
		P.ID
		, P.Code
		, P.UPC
		, P.Model
		, P.Name
		, P.Description
		, A.Manufacturer	 
		, A.Company
		, P.Price
		, P.ProductUrl
		, (SELECT TOP 1 ProductImage.Thumbnail FROM ProductImage WHERE ProductImage.ProductID = P.ID) as [ThumbImage]
		,A.[Total Viewed]
		FROM
		(
		SELECT TOP (@RecordToReturn)
		P.ID, M.Name AS [Manufacturer], C.Name AS [Company]
		,ISNULL(COUNT(P.ID),0) AS [Total Viewed]  
		FROM Product P
		INNER JOIN Manufacturer M ON M.ID = P.ManufacturerID 
		LEFT JOIN ProductVisitDetails PVD ON P.ID = PVD.ProductID
		INNER JOIN Company C ON P.CompanyID = C.ID
		WHERE P.CategoryID = @CategoryID
		AND P.ID <> @ProductID
		AND P.IsActive = 1
		AND P.IsDeleted = 0
		AND C.IsActive = 1
		GROUP BY P.ID,M.Name, C.Name
		ORDER BY [Total Viewed] DESC
		) A INNER JOIN Product P ON A.ID = P.ID
	END	
END
GO

-------------------------------------------------------------------------------
--	END OF CREATE STATEMENTS.
--	METADATA AND SUCCESS/ERROR MESSAGES BELOW.
-------------------------------------------------------------------------------
IF EXISTS (	SELECT *
			FROM INFORMATION_SCHEMA.ROUTINES
			WHERE ROUTINE_NAME = 'GetSimilarProducts'
				AND ROUTINE_TYPE = 'PROCEDURE' )
BEGIN
	PRINT 'Stored procedure GetSimilarProducts created successfully.';	
END
ELSE
BEGIN
	PRINT 'ERROR: Failed to create stored procedure GetSimilarProducts.';
END
GO



